package pos.data;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import pos.domain.Usuario;


public class JDBCUsuarioDAO implements IUsuarioDAO{
	
	public List selectAllUsuariosBarrio(Connection conn, Integer barrioOID) {

        PreparedStatement stmt = null;
        List searchResults = new LinkedList();
        ResultSet result = null;

        try {

                String sql = "SELECT nombre,nick,apellidos,password,email,telefono,fechaNac,cantidadProducto,donar,ropa,medicamentos,juguetes,alimentos,materialEscolar,municipio FROM Usuario U, Barrio B WHERE B.barrioOID=U.barrioOID and U.barrioOID = '"
                                + barrioOID + "'";

                stmt = conn.prepareStatement(sql);
                result = stmt.executeQuery();

                while (result.next()) {
                        Usuario u = new Usuario();
                        u.setNick(result.getString("nick"));
                        u.setApellidos(result.getString("apellidos"));
                        u.setPassword(result.getString("password"));
                        u.setEmail(result.getString("email"));
                        u.setTelefono(result.getString("telefono"));
                        u.setFechaNac(result.getString("fechaNac"));
                        u.setCantidadProducto(result.getInt("cantidadProducto"));
                        if (result.getInt("donar") == 1) {
                                u.setDonar(true);
                        } else {
                                u.setDonar(false);
                        }
                        
                        u.setRopa(result.getInt("ropa"));
        				u.setMedicamentos(result.getInt("medicamentos"));
        				u.setJuguetes(result.getInt("juguetes"));
        				u.setAlimentos(result.getInt("alimentos"));
        				u.setMaterialEscolar(result.getInt("materialEscolar"));
                        u.setMunicipio(result.getString("municipio"));
                        u.setBarrio(result.getString("nombre"));
                        searchResults.add(u);
                }
        } catch (SQLException e) {
                System.out.println("Message: " + e.getMessage());
                System.out.println("SQLState: " + e.getSQLState());
                System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
                try {
                        if (result != null)
                                result.close();
                        if (stmt != null)
                                stmt.close();
                } catch (SQLException e) {
                }

        }
        return searchResults;

}
	
	public List selectAllUsuariosEvento(Connection conn,	int eventoOID) {

		PreparedStatement stmt = null;
		List searchResults = new LinkedList();
		ResultSet result = null;

		try {

			String sql = "SELECT* FROM Usuario U, Usuarioevento UE, Evento E WHERE U.usuarioOID = UE.usuarioOID and E.eventoOID = UE.eventoOID AND E.eventoOID LIKE '"
					+ eventoOID + "'";
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			while (result.next()) {
				Usuario u = new Usuario();
				u.setNick(result.getString("nick"));
                u.setApellidos(result.getString("apellidos"));
                u.setPassword(result.getString("password"));
                u.setEmail(result.getString("email"));
                u.setTelefono(result.getString("telefono"));
                u.setFechaNac(result.getString("fechaNac"));
                u.setCantidadProducto(result.getInt("cantidadProducto"));
                if (result.getInt("donar") == 1) {
                        u.setDonar(true);
                } else {
                        u.setDonar(false);
                }
                u.setRopa(result.getInt("ropa"));
				u.setMedicamentos(result.getInt("medicamentos"));
				u.setJuguetes(result.getInt("juguetes"));
				u.setAlimentos(result.getInt("alimentos"));
				u.setMaterialEscolar(result.getInt("materialEscolar"));
				searchResults.add(u);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {

			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;

	}

	
	
	
	public List selectTodosUsuarios() {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		List searchResults = new LinkedList();
		ResultSet result = null;

		try {

			String sql = "SELECT nombre,nick,apellidos,password,email,telefono,fechaNac,cantidadProducto,donar,ropa,medicamentos,juguetes,alimentos,materialEscolar,municipio FROM Usuario U, Barrio B WHERE B.barrioOID=U.barrioOID ";
                                
			stmt = conn.prepareStatement(sql);

			result = stmt.executeQuery();

			while (result.next()) {
				 Usuario u = new Usuario();
                 u.setNick(result.getString("nick"));
                 u.setApellidos(result.getString("apellidos"));
                 u.setPassword(result.getString("password"));
                 u.setEmail(result.getString("email"));
                 u.setTelefono(result.getString("telefono"));
                 u.setFechaNac(result.getString("fechaNac"));
                 u.setCantidadProducto(result.getInt("cantidadProducto"));
                 if (result.getInt("donar") == 1) {
                         u.setDonar(true);
                 } else {
                         u.setDonar(false);
                 }
                 
                 u.setRopa(result.getInt("ropa"));
 				u.setMedicamentos(result.getInt("medicamentos"));
 				u.setJuguetes(result.getInt("juguetes"));
 				u.setAlimentos(result.getInt("alimentos"));
 				u.setMaterialEscolar(result.getInt("materialEscolar"));
                 u.setMunicipio(result.getString("municipio"));
                 u.setBarrio(result.getString("nombre"));
                 searchResults.add(u);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;
	}
	
	
	public Usuario selectUsuario(String userForm, String passwdForm) {

		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		ResultSet result = null;
		Usuario u = new Usuario();

		try {

			String sql = "SELECT nombre,nick,apellidos,password,email,telefono,fechaNac,cantidadProducto,donar,ropa,medicamentos,juguetes,alimentos,materialEscolar,municipio FROM Usuario U, Barrio B WHERE B.barrioOID=U.barrioOID and U.nick LIKE ' " 
				+ userForm + "' AND U.password LIKE  '" + passwdForm + "'";
			
			stmt = conn.prepareStatement(sql);
			stmt.executeQuery();
			result = stmt.executeQuery();

			if (result.next()) {

				 u.setNick(result.getString("nick"));
                 u.setApellidos(result.getString("apellidos"));
                 u.setPassword(result.getString("password"));
                 u.setEmail(result.getString("email"));
                 u.setTelefono(result.getString("telefono"));
                 u.setFechaNac(result.getString("fechaNac"));
                 u.setCantidadProducto(result.getInt("cantidadProducto"));
                 if (result.getInt("donar") == 1) {
                         u.setDonar(true);
                 } else {
                         u.setDonar(false);
                 }
                 
                 u.setRopa(result.getInt("ropa"));
 				u.setMedicamentos(result.getInt("medicamentos"));
 				u.setJuguetes(result.getInt("juguetes"));
 				u.setAlimentos(result.getInt("alimentos"));
 				u.setMaterialEscolar(result.getInt("materialEscolar"));
                 u.setMunicipio(result.getString("municipio"));
                 u.setBarrio(result.getString("nombre"));
               
				
			} else {
				u = null;
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return u;
	}

	
public void insertUsuario(String nick, String apellidos,String password,String email, String municipio,String barrio, String telefono, String fechaNac, int cantidadProducto, boolean donar,int ropa, int medicamentos, int juguetes, int alimentos, int materialEscolar, String motivo){
		
		Connection conn=ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;

		int don;
		if(donar){
			don=1;
		}
		else{
			don=0;
		}
		int num=(new JDBCUsuarioDAO()).contarUsuarios();
		int barrioNum=(new JDBCUsuarioDAO()).devuelveBarrioInt(barrio);
        String sql = "INSERT INTO Usuario ( usuarioOID, barrioOID,nick,apellidos, password, email,municipio,telefono,fechaNac,cantidadProducto,donar,ropa,medicamentos,juguetes,alimentos,materialEscolar,motivo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
        try {
            stmt = conn.prepareStatement(sql);

            stmt.setInt(1, num);
            stmt.setInt(2, barrioNum);
            stmt.setString(3, nick);
            stmt.setString(4, apellidos);
            stmt.setString(5, password);
            stmt.setString(6, email);
            stmt.setString(7, municipio);
            stmt.setString(8, telefono);
            stmt.setString(9, fechaNac);
            stmt.setInt(10, cantidadProducto);
            stmt.setInt(11, ropa);
            stmt.setInt(12, medicamentos);
            stmt.setInt(13, juguetes);
            stmt.setInt(14, materialEscolar);
            stmt.setString(15, motivo);
    
           
            

			stmt.executeUpdate();


		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

	}




public int contarUsuariosNo() {
	Connection conn = ConnectionManager.getInstance().checkOut();

	PreparedStatement stmt = null;
	ResultSet result = null;
	int res = 0;

	try {

		String sql = "SELECT count(*) FROM Usuario WHERE asiste=0";
		stmt = conn.prepareStatement(sql);
		stmt.executeQuery();
		result = stmt.executeQuery();

		if (result.next()) {

			res = result.getInt("count(*)");

		}
	} catch (SQLException e) {
		System.out.println("Message: " + e.getMessage());
		System.out.println("SQLState: " + e.getSQLState());
		System.out.println("ErrorCode: " + e.getErrorCode());
	} finally {
		ConnectionManager.getInstance().checkIn(conn);
		try {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		} catch (SQLException e) {
		}

	}
	return res;
}

public int contarUsuariosNoE(String motivo) {
	Connection conn = ConnectionManager.getInstance().checkOut();

	PreparedStatement stmt = null;
	ResultSet result = null;
	int r = 0;

	try {

		String sql = "SELECT count(*) FROM Usuario WHERE asiste=0 AND motivo LIKE '"
				+ motivo + "'";
		stmt = conn.prepareStatement(sql);
		stmt.executeQuery();
		result = stmt.executeQuery();

		if (result.next()) {

			r = result.getInt("count(*)");

		}
	} catch (SQLException e) {
		System.out.println("Message: " + e.getMessage());
		System.out.println("SQLState: " + e.getSQLState());
		System.out.println("ErrorCode: " + e.getErrorCode());
	} finally {
		ConnectionManager.getInstance().checkIn(conn);
		try {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		} catch (SQLException e) {
		}

	}
	return r;
}

public List selectAllUsuariosBar(Connection conn, Integer barrioOID) {

	PreparedStatement stmt = null;
	List searchResults = new LinkedList();
	ResultSet result = null;

	try {

		String sql = "SELECT nombre,nick,apellidos,password,email,telefono,fechaNac,cantidadProducto,donar,ropa,medicamentos,juguetes,alimentos,materialEscolar,municipio FROM Usuario U,Barrio B WHERE b.barrioOID=U.barrioOID and U.barrioOID = '" + barrioOID + "'";

		stmt = conn.prepareStatement(sql);
		result = stmt.executeQuery();

		while (result.next()) {
			Usuario u = new Usuario();

			 u.setNick(result.getString("nick"));
            u.setApellidos(result.getString("apellidos"));
            u.setPassword(result.getString("password"));
            u.setEmail(result.getString("email"));
            u.setTelefono(result.getString("telefono"));
            u.setFechaNac(result.getString("fechaNac"));
            u.setCantidadProducto(result.getInt("cantidadProducto"));
            if (result.getInt("donar") == 1) {
                    u.setDonar(true);
            } else {
                    u.setDonar(false);
            }
            
            u.setRopa(result.getInt("ropa"));
			u.setMedicamentos(result.getInt("medicamentos"));
			u.setJuguetes(result.getInt("juguetes"));
			u.setAlimentos(result.getInt("alimentos"));
			u.setMaterialEscolar(result.getInt("materialEscolar"));
            u.setMunicipio(result.getString("municipio"));
            u.setBarrio(result.getString("nombre"));
            searchResults.add(u);
		}
	} catch (SQLException e) {
		System.out.println("Message: " + e.getMessage());
		System.out.println("SQLState: " + e.getSQLState());
		System.out.println("ErrorCode: " + e.getErrorCode());
	} finally {
		try {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		} catch (SQLException e) {
		}

	}
	return searchResults;

}
public int contarUsuarios(){
	Connection conn = ConnectionManager.getInstance().checkOut();

	PreparedStatement stmt = null;
	ResultSet result = null;
	int enc=0;

	try {
		
		String sql = "SELECT count(*) FROM Usuario ";
		stmt = conn.prepareStatement(sql);
		stmt.executeQuery();
		result = stmt.executeQuery();

		if (result.next()) {

			enc=result.getInt("count(*)");

		}
	} catch (SQLException e) {
		System.out.println("Message: " + e.getMessage());
		System.out.println("SQLState: " + e.getSQLState());
		System.out.println("ErrorCode: " + e.getErrorCode());
	} finally {
		ConnectionManager.getInstance().checkIn(conn);
		try {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		} catch (SQLException e) {
		}

	}
	return enc;
}
			


public int devuelveBarrioInt(String barrio){
	Connection conn = ConnectionManager.getInstance().checkOut();

	PreparedStatement stmt = null;
	ResultSet result = null;
	int enc=0;

	try {
		
		String sql = "SELECT barrioOID FROM Barrio where nombre LIKE '"+barrio+"' ";
		stmt = conn.prepareStatement(sql);
		stmt.executeQuery();
		result = stmt.executeQuery();

		if (result.next()) {

			enc=result.getInt("barrioOID");

		}
	} catch (SQLException e) {
		System.out.println("Message: " + e.getMessage());
		System.out.println("SQLState: " + e.getSQLState());
		System.out.println("ErrorCode: " + e.getErrorCode());
	} finally {
		ConnectionManager.getInstance().checkIn(conn);
		try {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		} catch (SQLException e) {
		}

	}
	return enc;
}
}

	
	
	
	
	
	
	
	
	
	
	
	
